<?php
$servername = "localhost";
$username = "root";
$password = "";

//Create Connection
$conn = new mysqli($servername, $username, $password);

//Check connection
if($conn->connect_error){
	die("Connection failed: " . $conn->conect_error);
}


//Create Database and tables schema. Queries are from real_estate.sql This code has to be executed only once

$sql = "
	CREATE DATABASE IF NOT EXISTS real_estate_db;
	USE real_estate_db;

	CREATE TABLE IF NOT EXISTS users(
    	id INT(10) AUTO_INCREMENT PRIMARY KEY,
    	login VARCHAR(20) UNIQUE NOT NULL,
    	type VARCHAR(10) NOT NULL DEFAULT 'customer',
    	pass VARCHAR(255) NOT NULL
	);
	
	CREATE TABLE IF NOT EXISTS properties(
    	id INT(10) AUTO_INCREMENT PRIMARY KEY,
    	user_id INT(10) NOT NULL,
    	bedrooms INT(10) NOT NULL,
    	price INT(10) NOT NULL,
    	description VARCHAR(1500) NOT NULL DEFAULT 'N/A',
    	date_listed TIMESTAMP DEFAULT NOW(),
    	FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE
	);

	CREATE TABLE IF NOT EXISTS addresses(
    	property_id INT(10) PRIMARY KEY,
    	city VARCHAR(40) NOT NULL,
    	suburb VARCHAR(40) NOT NULL,
    	street_name VARCHAR(40) NOT NULL,
    	street_number INT(10) NOT NULL,
    	FOREIGN KEY(property_id) REFERENCES properties(id) ON DELETE CASCADE
	);

	CREATE TABLE IF NOT EXISTS photos(
    	id INT(10) AUTO_INCREMENT PRIMARY KEY,
    	property_id INT(10) NOT NULL,
    	photo_url VARCHAR(255) NOT NULL UNIQUE,
    	FOREIGN KEY(property_id) REFERENCES properties(id) ON DELETE CASCADE
	);

	CREATE TABLE IF NOT EXISTS watchlists(
    	property_id INT(10),
    	user_id INT(10),
    	FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE,
    	FOREIGN KEY(property_id) REFERENCES properties(id) ON DELETE CASCADE,
    	PRIMARY KEY(property_id, user_id)
	);
	
-- This adds default admin user with pass: 'admin'
-- and should be changed later from admin's page.
	 	INSERT  INTO users(login, type, pass)
	 	VALUES('admin', 'admin', 'admin')
	 	ON DUPLICATE KEY UPDATE id = id
";
		

if($conn->multi_query($sql) == FALSE){
	echo "Error creating DB!<br>";
}
